/*
	EXEC SP_CANCEL_PACKING_SLIP 158, 'CANCELLED', 'admin'
*/
ALTER        proc sp_cancel_packing_slip
@customerShipping_id int,
@status varchar(20),
@USER_ID VARCHAR(50)
As
begin

	/* UPDATE STATUS TO CANCEL */
	declare @cslit_cursor CURSOR
	declare @cslit_id int
	declare @ps_status varchar(20)
	declare @pull_qty int

	SELECT  @ps_status = CST_Status FROM Customer_Shipping_Table WHERE CustomerShipping_ID = @customerShipping_id
	
	IF @ps_status = 'ISSUED'
	BEGIN
		update customer_shipping_table set cst_status = @status, CST_CI_Show_Invoice = 1, CST_UPDATED_BY = @USER_ID, CST_UPDATED_ON = CURRENT_TIMESTAMP where customershipping_id = @customerShipping_id
		update customer_shipping_line_item_table set cslit_Status = @status where customershipping_id = @customerShipping_id
		
		set @cslit_cursor = CURSOR FOR
		select cslit_id from customer_shipping_line_item_table where customershipping_id = @customerShipping_id
		open @cslit_cursor
			fetch next from @cslit_cursor into @cslit_id
			while @@fetch_status = 0
			begin
				update customer_shipping_lot_number_table set cslnt_status = @status where cslit_id = @cslit_id

				DELETE FROM DISTRIBUTOR_INVENTORY_LOT WHERE cslit_id = @cslit_id

				fetch next from @cslit_cursor into @cslit_id
			end
		close @cslit_cursor
		deallocate @cslit_cursor
	
	
		/* RETURN QUANTITY TO INVENTORY_LOT */
		declare @line_item_id int
		declare @line_item_id_cursor cursor 
		declare @lot_number_info_cursor cursor
		declare @lot_id int
		
		declare @balance_qty int
		declare @shipped_qty int
		declare @lot_current_status varchar(20)--Bhaskar's addition on 30 october
	
		set @line_item_id_cursor = cursor for
		select cslit_id from customer_shipping_line_item_table where customerShipping_id = @customerShipping_id
		open @line_item_id_cursor
			fetch next from @line_item_id_cursor into @line_item_id
			while @@fetch_status = 0
			begin 
				set @lot_number_info_cursor = cursor for
				select Lot_ID, CSLNT_Qty_Pull from customer_shipping_lot_number_table where cslit_id = @line_item_id
				open @lot_number_info_cursor
					fetch next from @lot_number_info_cursor into @lot_id, @pull_qty
					while @@fetch_status = 0
					begin
						select @shipped_qty = IL_Qty_Shipped, @balance_qty = IL_Qty_Balance From Inventory_Lot Where Lot_ID = @lot_id
						update Inventory_lot set IL_Qty_Shipped = (@shipped_qty-@pull_qty), IL_Qty_Balance = (@balance_qty+@pull_qty) Where Lot_ID = @lot_id
						--Bhaskar's addition starts 30 oct 2003
						if  @balance_qty+@pull_qty > 0 
						begin
							select @lot_current_status = lot_current_status from lot_table where lot_id = @lot_id
							if @lot_current_status = 'Close'
							begin
								update Lot_Table set lot_current_status = 'Running' where Lot_ID = @lot_id
							end
						end
						--Bhaskar's addition ends 30 oct 2003
						fetch next from @lot_number_info_cursor into @lot_id, @pull_qty
					end
				close @lot_number_info_cursor
				deallocate @lot_number_info_cursor
				fetch next from @line_item_id_cursor into @line_item_id
			end
		close @line_item_id_cursor
		deallocate @line_item_id_cursor
	END
	ELSE IF ( @ps_status = 'SHIPPED' ) --CANCELLATION OF A SHIPPED PACKING SLIP
	 BEGIN
		DECLARE @LOT_CURSOR CURSOR
		DECLARE @LINEITEM_CURSOR CURSOR
		DECLARE @IS_CONSIGNMENT_SALES INT
		DECLARE @FG_LOCATION_ID INT
		DECLARE @LOT_NUMBER VARCHAR(50)
		DECLARE @CONSIGNMENT_LOT_ID INT
		DECLARE @CUSTOMER_ID INT
		DECLARE @FG_VENDOR_NUMBER VARCHAR(50)
		DECLARE @QTY_IN INT
		DECLARE @COOL_LINE_ITEM_NO VARCHAR(10)
		DECLARE @SALESORDER_ID INT
		DECLARE @TEMP_TOTAL_QTY_SHIPPED INT
		DECLARE @TEMP_ACTION VARCHAR(100)
		DECLARE @LINEITEM_ID INT
		DECLARE @COOL_QTY INT

		UPDATE CUSTOMER_SHIPPING_TABLE SET CST_STATUS = @STATUS, CST_CI_SHOW_INVOICE = 1, CST_UPDATED_BY = @USER_ID, CST_UPDATED_ON = CURRENT_TIMESTAMP WHERE CUSTOMERSHIPPING_ID = @CUSTOMERSHIPPING_ID
		UPDATE CUSTOMER_SHIPPING_LINE_ITEM_TABLE SET CSLIT_STATUS = @STATUS WHERE CUSTOMERSHIPPING_ID = @CUSTOMERSHIPPING_ID
		DELETE FROM DISTRIBUTOR_INVENTORY_LOT WHERE CSLIT_ID IN ( SELECT CSLIT_ID FROM CUSTOMER_SHIPPING_LINE_ITEM_TABLE WHERE CUSTOMERSHIPPING_ID = @CUSTOMERSHIPPING_ID )
		UPDATE CUSTOMER_SHIPPING_LOT_NUMBER_TABLE SET CSLNT_STATUS = @STATUS WHERE CSLIT_ID IN ( SELECT CSLIT_ID FROM CUSTOMER_SHIPPING_LINE_ITEM_TABLE WHERE CUSTOMERSHIPPING_ID = @CUSTOMERSHIPPING_ID )

		SELECT @IS_CONSIGNMENT_SALES = SO.SO_IS_CONSIGNMENT, @CUSTOMER_ID = SO.CUSTOMER_ID FROM CUSTOMER_SHIPPING_TABLE CST, SALES_ORDER SO WHERE CST.SALESORDER_ID = SO.SALESORDER_ID AND CUSTOMERSHIPPING_ID = @CUSTOMERSHIPPING_ID

		SET @LOT_CURSOR = CURSOR FOR
				SELECT CSLIT.CSLIT_ID, CSLNT.LOT_ID, CSLNT.CSLNT_QTY_PULL
						FROM CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT, 
							CUSTOMER_SHIPPING_LOT_NUMBER_TABLE CSLNT,
							CUSTOMER_OPEN_ORDER_LINEITEM COOL
						WHERE CSLNT.CSLIT_ID = CSLIT.CSLIT_ID
							AND CSLIT.CUSTOMERSHIPPING_ID = @CUSTOMERSHIPPING_ID
							AND COOL.LINEITEM_ID = CSLIT.LINEITEM_ID
		OPEN @LOT_CURSOR
		FETCH NEXT FROM @LOT_CURSOR INTO @CSLIT_ID, @LOT_ID, @PULL_QTY
		WHILE @@FETCH_STATUS = 0
		 BEGIN
			SELECT @SHIPPED_QTY = IL_QTY_SHIPPED, @BALANCE_QTY = IL_QTY_BALANCE FROM INVENTORY_LOT WHERE LOT_ID = @LOT_ID
			UPDATE INVENTORY_LOT SET IL_QTY_SHIPPED = (@SHIPPED_QTY-@PULL_QTY), IL_QTY_BALANCE = (@BALANCE_QTY+@PULL_QTY) WHERE LOT_ID = @LOT_ID			
			UPDATE LOT_TABLE SET QTY_IN = QTY_IN + @PULL_QTY, LOT_CURRENT_STATUS = 'Running' WHERE LOT_ID = @LOT_ID

			DELETE FROM DISTRIBUTOR_INVENTORY_LOT WHERE CSLIT_ID = @CSLIT_ID
			IF ( @IS_CONSIGNMENT_SALES = 0 )
			 BEGIN
				--NEED TO DEDUCT THE SHIPPED INVENTORY FROM THE CUSTOMER VENDOR LOCATION
				SELECT @FG_VENDOR_NUMBER = C_NUMBER FROM CUSTOMER WHERE CUSTOMER_ID = @CUSTOMER_ID
				SELECT 	@FG_LOCATION_ID = LOCATION_ID FROM LOCATION L, VENDOR V WHERE L.VENDOR_ID = V.VENDOR_ID 
													AND V.V_NUMBER = @FG_VENDOR_NUMBER 											
													AND L.L_TYPE = 'Finished Goods'
													AND L.L_Is_Deleted='N' 
				IF ( @FG_LOCATION_ID IS NOT NULL )
				 BEGIN
					SELECT @LOT_NUMBER = LOT_NUMBER FROM LOT_TABLE WHERE LOT_ID = @LOT_ID
					SELECT @CONSIGNMENT_LOT_ID = LOT_ID, @QTY_IN = QTY_IN FROM LOT_TABLE WHERE LOCATION_ID = @FG_LOCATION_ID AND LOT_NUMBER = @LOT_NUMBER
					IF ( @CONSIGNMENT_LOT_ID IS NOT NULL )
					 BEGIN
						IF ( @QTY_IN >= @PULL_QTY )
						 BEGIN
							UPDATE LOT_TABLE SET QTY_IN = (@QTY_IN-@PULL_QTY) WHERE LOT_ID = @CONSIGNMENT_LOT_ID
							UPDATE INVENTORY_LOT SET IL_QTY_IN = (@QTY_IN-@PULL_QTY), IL_QTY_BALANCE = (ISNULL(IL_QTY_BALANCE,@QTY_IN)-@PULL_QTY) WHERE LOT_ID = @CONSIGNMENT_LOT_ID
							IF ( (@QTY_IN-@PULL_QTY) = 0 )
							 BEGIN
								UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = 'Close' WHERE LOT_ID = @CONSIGNMENT_LOT_ID
							 END
						 END
						/*ELSE --CAN'T CANCEL THIS PACKING SLIP AS CONSIGNMENT IS SHIPPED, TO CANCEL THIS FIRST CANCEL THE CONSIGNMENT LOCATION SHIPMENT PACKING SLIP
						 BEGIN
							--TODO
						 END*/
					 END
				 END
			 END
			SET @SHIPPED_QTY = 0
			SET @BALANCE_QTY = 0
			SET @FG_VENDOR_NUMBER = NULL
			SET @FG_LOCATION_ID = 0
			SET @LOT_NUMBER = NULL
			SET @CONSIGNMENT_LOT_ID = 0
			SET @QTY_IN = 0
			SET @CSLIT_ID = 0
			SET @LOT_ID = 0
			SET @PULL_QTY = 0
	
			FETCH NEXT FROM @LOT_CURSOR INTO @CSLIT_ID, @LOT_ID, @PULL_QTY
	 	 END
		CLOSE @LOT_CURSOR
		DEALLOCATE @LOT_CURSOR

		SET @LINEITEM_CURSOR = CURSOR FOR
					SELECT COOL.SALESORDER_ID, COOL.LINEITEM_ID, COOL.COOL_QTY, COOL.COOL_LINE_ITEM_NO
						FROM CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT,							
							CUSTOMER_OPEN_ORDER_LINEITEM COOL
						WHERE CSLIT.CUSTOMERSHIPPING_ID = @CUSTOMERSHIPPING_ID
							AND COOL.LINEITEM_ID = CSLIT.LINEITEM_ID
		OPEN @LINEITEM_CURSOR
		FETCH NEXT FROM @LINEITEM_CURSOR INTO @SALESORDER_ID, @LINEITEM_ID, @COOL_QTY, @COOL_LINE_ITEM_NO
		WHILE @@FETCH_STATUS = 0
		 BEGIN
			SELECT @TEMP_TOTAL_QTY_SHIPPED = SUM(CSLIT_QTY_SHIPPED) FROM CUSTOMER_SHIPPING_LINE_ITEM_TABLE 
			WHERE LINEITEM_ID = @LINEITEM_ID
			AND  CSLIT_STATUS = 'SHIPPED' 
			GROUP BY LINEITEM_ID
			IF ( ISNULL(@TEMP_TOTAL_QTY_SHIPPED,0) = 0 ) --WHOLE LINE ITEM QTY SHIPMENT IS CANCELLED
		 	 BEGIN
				UPDATE CUSTOMER_OPEN_ORDER_LINEITEM SET COOL_STATUS = 'APPROVED' WHERE LINEITEM_ID = @LINEITEM_ID
				SET @TEMP_ACTION =  '<br> Line Item No: '+ @COOL_LINE_ITEM_NO + ' Shipment is CANCELLED'
			 END
			ELSE --LINE ITEM PARTIAL SHIPMENT QTY IS CANCELLED
			 BEGIN
				UPDATE CUSTOMER_OPEN_ORDER_LINEITEM SET COOL_STATUS = 'APPROVED' WHERE LINEITEM_ID = @LINEITEM_ID
				SET @TEMP_ACTION =  '<br> Line Item No: '+ @COOL_LINE_ITEM_NO + ' Partial Shipment is CANCELLED'
			 END
			INSERT INTO SALES_AUDIT_TRAIL(SALESORDER_ID, USER_ID, ACTIONS, COMMENTS, TIMESTAMP) VALUES (@SALESORDER_ID,@USER_ID,@TEMP_ACTION,NULL,CURRENT_TIMESTAMP)
			
			SET @LINEITEM_ID = 0
			SET @SALESORDER_ID = 0
			SET @COOL_QTY = 0
			SET @COOL_LINE_ITEM_NO = NULL
			FETCH NEXT FROM @LINEITEM_CURSOR INTO @SALESORDER_ID, @LINEITEM_ID, @COOL_QTY, @COOL_LINE_ITEM_NO
		 END
		CLOSE @LINEITEM_CURSOR
		DEALLOCATE @LINEITEM_CURSOR
	 END
	ELSE
	BEGIN
		--This dummy updating is for avoiding SQL Exception. --Added by KumaraSwamy(11-22-04)
		UPDATE Customer_Shipping_Table SET Label = 'Label' WHERE Label = 'Label' AND CustomerShipping_ID = @customerShipping_id
	END
end
